﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WebsiteComponent.BLL;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using System.Data.SqlClient;

namespace WebsiteComponent.DAL
{
    public class WebsiteDAL:IWebsiteDAL
    {
        #region Connection
        private string _stringconnection=Commom.WCManager.ConnectionString();

        public string Connection
        {
            get { return _stringconnection; }
            set { _stringconnection = value; }
        }
        
        #endregion

        #region IWebsite Method
        #region Get
        public void Get(Website website)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(this.Connection))
                {
                    SqlParameter[] par = new SqlParameter[1];
                    par[0] = new SqlParameter("@website_id", website.Website_id);
                    using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, "P_website_Get", par))
                    {
                        while (dr.Read())
                        {
                            website.Website_id = int.Parse(dr["website_id"].ToString());
                            website.Ten = dr["ten"].ToString();
                            website.Url=dr["url"].ToString();
                            website.Path=dr["path"].ToString();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
            }
        }
        #endregion

        #region Add
        public int Add(Website website)
        {
            
            try
            {
                int rowNo = -1;
                using (SqlConnection conn = new SqlConnection(this.Connection))
                {
                    SqlParameter[] par = new SqlParameter[4];
                    par[0]= new SqlParameter("@website_id",website.Website_id);
                    par[0].Direction=ParameterDirection.Output;
                    par[1]= new SqlParameter("@ten",website.Ten);
                    par[2]=new SqlParameter("@url",website.Url);
                    par[3]=new SqlParameter("@path",website.Path);
                    SqlHelper.ExecuteNonQuery(conn,CommandType.StoredProcedure,"P_website_Insert",par);
                    rowNo = int.Parse(par[0].Value.ToString());
                }
                return rowNo;
            }
            catch (Exception ex)
            {
                throw;
            }
            
        }
        #endregion

        #region Delete
        public void Delete(Website website)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(this.Connection))
                {
                    SqlParameter[] par = new SqlParameter[1];
                    par[0] = new SqlParameter("@website_id", website.Website_id);
                    SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "P_website_Delete", par);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        #endregion

        #region Update
        public void Update(Website website)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(this.Connection))
                {
                    SqlParameter[] par = new SqlParameter[4];
                    par[0]= new SqlParameter("@website_id",website.Website_id);
                    par[0].Direction=ParameterDirection.Output;
                    par[1]= new SqlParameter("@ten",website.Ten);
                    par[2]=new SqlParameter("@url",website.Url);
                    par[3]=new SqlParameter("@path",website.Path);
                    SqlHelper.ExecuteNonQuery(conn,CommandType.StoredProcedure,"P_website_Update",par);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        #endregion

        #region GetAll
        public List<Website> GetAll(int currentPage, int pageSize, out int total, string ten, string column_sort, string type_sort)
        {
            try
            {
                List<Website> soList = new List<Website>();
                using (SqlConnection con = new SqlConnection(this.Connection))
                {
                    SqlParameter[] par = new SqlParameter[6];
                    par[0] = new SqlParameter("@currentpage", currentPage);
                    par[1] = new SqlParameter("@pagesize", pageSize);
                    par[2] = new SqlParameter("@total", SqlDbType.Int);
                    par[2].Direction = ParameterDirection.Output;
                    par[3] = new SqlParameter("@solution_name", ten);
                    par[4] = new SqlParameter("@column_sort", column_sort);
                    par[5] = new SqlParameter("@type_sort", type_sort);

                    using (SqlDataReader dr = SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "P_Solutions_GetAll", par))
                    {
                        while (dr.Read())
                        {
                            Website website = new Website();
                            website.Website_id = int.Parse(dr["website_id"].ToString());
                            website.Ten = dr["ten"].ToString();
                            website.Url=dr["url"].ToString();
                            website.Path=dr["path"].ToString();
                        }
                    }
                    total = int.Parse(par[2].Value.ToString());
                }
                return soList;
            }
            catch (Exception ex) { throw; }
        }
        #endregion
        #endregion
    }
}
